Import transfers from Sage Cash Management
This function is used to import matching transfers from the Cash Management software, and to transform them into accounting journals.
The structure of the transfer file being very different from that of the journal postings, the import is sub-divided into 2 successive and linked stages:
- first, import of the file data into a dedicated table (BNKTRSIMP),
- then application of the standard accounting posting to the data in the table, and generation of the corresponding journal postings
via the automatic journal of the GAUCASH setup)
In case of problems arising during one of the two phases, the file is rejected as a whole.
Prerequisite
Refer to documentation Implementation
Screen management
Entry screen
Fields
The following fields are present on this tab :
File generation
|
Select Client to generate the extraction files in a folder on your computer or workstation. Select Server to generate the extraction files on a network or shared folder. Enter or select the server location (Access path) in the Directory field. |
|
Name of the file to import. For 'Server' destination, click the Selection icon to open the list of available volumes. In this list, you can select a directory/file. |
|
If this tick box is ticked, a process generates an import trace of the balancing transfer file. |
Close
Structure of the imported file
The transfer file is comprised of a minimum of six or seven types of recordings:
- Recording 0001 Header line
-> header recording containing mainly the accounting date of the transfers - Recording 0002 - Date and support line
-> recording containing header data from journals, such as the company, the document type, the value date and a description - Recording 0003 - Offset line
-> recording containing the data of the offset lines (account 5800 or 4510), such as the account, the amount, the description, the sense, the BP, a line reference code in company currency - Recording 0004 - Bank and account line
-> recording containing the data of the bank line (account 5120), such as the account, the amount, the description, the sense, the BP, a line reference code in company currency - Recording 0005 - Currency line
-> recording containing the transfer amount in bookkeeping currency, the bookkeeping currency
-> recording only presented if and only if the bookkeeping currency is different from the company currency. - Recording 0009 Summary line
-> recording summarizing the journal, mainly containing the journal number - Recording 9999 Total line
-> recording summarizing all the transfers contained in the file
Depending on the bookkeeping currency, it is possible to have the following structures:
Bookkeeping currency <> Company currency | Bookkeeping currency = Company currency |
0001 Header line | 0001 Header line |
0002 Date and support line | 0002 Date and support line |
0003 Offset line | 0003 Offset line |
0004 Bank and account line | 0004 Bank and account line |
0005 Currency line | 0009 Summary line |
0009 Summary line | 0002 Date and support line |
0002 Date and support line | 0003 Offset line |
0003 Offset line | 0004 Bank and account line |
0004 Bank and account line | 0009 Summary line |
0005 Currency line | 9999 Total line |
0009 Summary line |
|
9999 Total line |
|
These 2 files each generate two journal postings on the two bank accounts between which the transfer has been carried out.
It is possible to have a file displaying both types of structures according to the currencies concerned.
Setup specificities
The implementation of the matching transfer import requires that certain setup pre-requisites be taken into account at e-Cash level:
Recordings | e-Cash fields | Information expected from Sage X3 |
Recording 0002 - Date and support line | Company matching code | The contents of this field must match the Sage X3 company code. |
Recording 0002 - Date and support line | Support matching code | The contents of this field must match the Sage X3 journal type to be used (Disbursement or Cash receipt depending on the support sign). |
Recording 0003 - Offset line | Nature matching code | The contents of this field must match the Sage X3 account number for the pending transfer (5800) or the group current account (4510). |
Recording 0003 - Offset line | Annex code matching code | When the contra account is a control account, then this field must contain the code of the associated BP. |
Recording 0004 - Bank and account line | Account matching code | The first 3 characters of this field are dedicated to the the information on the journal site. Then, from the 4th character, the bank account number must be entered. |
Recording 0005 - Currency line | Currency matching code | This field is used to enter the code of the currency to be used as the journal currency. |
Structure of the imported file
The transfer file is comprised of N recordings, N being an even number. In effect, the lines go by two, each group of two lines representing a transfer entry (for instance 512 to 580).
No. | Field name | Comments vs | Position | Length | Type |
1 | Journal code |
| 1 | 3 | Alphanum. |
2 | Journal date | Matches the Sage X3 account date. | 4 | 6 | DDMMYY format date |
3 | Entry type | The Sage 1000 Cash Management journal type is linked to the [bank account+company] combination. So it is not dependent on the transaction sign, whereas, in most cases, Sage X3 has available a Receipt and an Expense journal type. The setup of the standard CASH automatic journal plans to retrieve this value (therefore, to be created) but this can be modified. | 10 | 2 | Alphanum. |
4 | GL account | Either the cash account (X3) or the internal transfer account or the current account. | 12 | 13 | Alphanum. |
5 | Account type | This field should not be taken into account. | 25 | 1 | Alphanum. |
6 | Auxiliary account or dimension | This field should not be taken into account. | 26 | 13 | Alphanum. |
7 | Entry reference | The setup of the standard CASH automatic journal plans to retrieve this field as the original document. | 39 | 13 | Alphanum. |
8 | Entry description | The setup of the standard CASH automatic journal plans to retrieve this information in the default description in the journal header but also in the description on the line. | 52 | 25 | Alphanum. |
9 | Payment mode | This field should not be taken into account. | 77 | 1 | Alphanum. |
10 | Due date | Corresponds to the due date mentioned in the journal header. | 78 | 6 | DDMMYY format date |
11 | Sign | The file to be integrated contains D for Debit and C for Credit. During import, these values will be respectively transformed into 1 and -1 to match the values of the Sense field of Sage X3. | 84 | 1 | Alphanum. |
12 | Line amount company currency | Corresponds to the forced company amount. | 85 | 20 | Numeric |
13 | Entry type | This field should not be taken into account. | 105 | 1 | Alphanum. |
14 | Document no. | The file contains N lines grouped by 2 because they have the same item number. Each group of 2 lines having the same document number will be turned into a Sage X3 document. The standard CASH automatic journal plans to retrieve this number and use it as the journal number, but this can be modifed. | 106 | 7 | Alphanum. |
15 | Reserved field | This field should not be taken into account. | 113 | 26 | Alphanum. |
16 | ISO code company currency | This field should not be taken into account. | 139 | 3 | Alphanum. |
17 | Line amount transaction currency | This field should not be taken into account. | 142 | 20 | Numerical. |
18 | ISO code transaction currency | This field should not be taken into account. | 162 | 3 | Alphanum. |
19 | Cash company code | This field should not be taken into account. | 165 | 15 | Alphanum. |
20 | Branch | Corresponds to the site of the entry line. | 180 | 15 | Alphanum. |
21 | Amount currency line bank account | The entry line is taken into account as the transaction currency amount. | 195 | 20 | Numeric |
22 | ISO code bank account currency | The entry is taken into account as the transaction currency. | 215 | 3 | Alphanum. |
|
| Number of characters/recording = | 218 |
|
|
Structure of the Sage FRP Treasury file
No. | Field name | Comments | Examples | Position | Length |
1 | Account | Either the cash account (X3) or the internal transfer account or the current account. | 512100 | 1 | 100 |
2 | Company | X3 company code contained in the import file. | APN | 101 | 8 |
3 | Management | The file to be integrated contains D for 'Debit' and C for 'Credit'. | E | 109 | 1 |
4 | Date | Matches the Sage X3 account date. | 07092009 | 110 | 8 |
5 | Amount in company currency | Matches the company amount. | 1234567890123.45 | 118 | 20 |
6 | Company currency | Currency of the main social ledger. | EUR | 138 | 3 |
7 | Amount in operation currency | Amount expressed in the transaction currency. | 1234567890123.45 | 141 | 20 |
8 | Transaction currency | ISO code of the transaction currency. | EUR | 161 | 3 |
9 | Reference | Loading of field REF in Sage X3. | Operation no. 3669 | 164 | 10 |
10 | Description (comments) | Label of the entry line. | Balancing transfer from BNP tp SG | 174 | 30 |
11 | G/L Type | This field specifies the document type, for instance 'ENC' ou 'DEC'. | DEC | 204 | 10 |
12 | Journal code | The journal code is deduced from the bank account. This read is made possible thanks to the CASH automatic document. | BNP | 214 | 10 |
13 | Cash Entry Site No | This field should not be taken into account. | Empty | 224 | 3 |
14 | Cash Entry Log No | This field should not be taken into account. | Empty | 227 | 5 |
15 | Cash Entry Internal No | Can be used to determine the document number | 3669 | 232 | 10 |
16 | G/L Entry No | This field should not be taken into account. | Empty | 242 | 4 |
Batch task
This function can be run in batch mode. The standard task BNKTRSIMP is provided for that purpose.
Error messages
In addition to the generic error messages, the following messages can appear during the entry :
"Recording code does not exist XXXXX"
This message appears when the first 4 characters at the beginning of a line do not match those that are usually expected (0001/ 0002/0003/0004/0005/0009/9999).
"No issuer recording"
This message appears when the 0001 header recording cannot be found in the file to be imported.
"Structure of lines incorrect"
This message appears for the other structure problems (a missing 0002 recording, two 0003 recordings before a 0004 recording,...)
"Currency does not exist"
This message appears if the currency code of the 0005 recording is not correct.
"Non-existent company"
This message appears if the company code of the 0002 recording is not correct.
"Amounts equal to zero"
This information message appears if a transfer amount is null (and thus a journal amount). Then, depending on the value of the SIVNULL setup (Common Data chapter), the journal is or is not generated.
"File not imported"
This message appears when one of the previously mentioned errors arises (incorrect line structure, non-existent recording code,...)
"Journal not generated"
This message appears when the generation of the entry, via the automatic journal of the GAUCASH setup, has failed
The posted documents will have the 'Final' status for the company
If the processing is launched for a given company and this company has selected Conformity DGI no. 13L-1-06 (i.e. the value of setup FRADGI/chapter LOC/group FRA is 'yes'), the documents generated by the processing will have the status 'Final'.
The posted documents will have the 'Final' status for one or more companies
The processing is launched for all companies or a group of companies, and if at least one of the companies chose Conformity DGI no. 13L-1-06 (i.e. the value of setup FRADGI/chapter LOC/group FRA is 'yes'), the documents generated by the processing will have the status 'Final'.